#region Using directives
using System;
using System.Data;

using WP.Commons;
using WP.DB;
using WP.MF.DataEntity;
#endregion

namespace WP.MF.DataAccess
{

    /*
     * Created by SmartCreateCode Tool
     * At 4/2/2005
     */

    public class MLocationDAL : IDataAccess
    {
        public MLocationDAL()
        {
        }

        #region Select List
        public string SQLList(int iParameter)
        {
            //string strSQL = @"SELECT RecId,LocationNo,LocationDesc,BranchNo,ProfitNo,(case when DirectOnly=1 then 'Yes' else 'No' end) as DirectOnly,(case when Active=1 then 'Yes' else 'No' end) as Active FROM mLocation order by LocationNo";
            string strSQL = @"SELECT RecId,LocationNo,LocationDesc,BranchNo,ProfitNo,DirectOnly, Active FROM mLocation order by LocationNo";
            return strSQL;
        }
        #endregion
         
        #region FieldNames
        public string FieldNames
        {
            get
            {
                return @"LocationNo, LocationDesc,BranchNo,ProfitNo,DirectOnly,RemanLocation,TransferLocation,Active,NeedUseActualCost,IsInterCompany,CommissionasDirectLocation
				,DefCreateImLocations,CurNo,MasterBuyLocation,SurchargePct,GoodsRecvSurPct,GoodsRecvSurAcctNo,GoodsRecvSurProfitNo,GoodsRecvSurBranchNo,ByEfftWtavgCostForGM,AutoFrtCost
				,MainCashier,SlsShortLocation, TransitLocation, MillSlsLocation, VmiLocation,VmiType,VmiUser,VmiCustNo,VmiShipToNo,FreeDockDays, WharfageRateByCrate, BrokerSupNo, MillSlsTranfCust, MillSlsDesc,RemanUser,OfficeUser,TaxCode
				,Companyname,Street, City, ProvNo, PostalCode, LocationZone, Fax, Phone,Contact,Email, PrtPrice,PrtOeTrailRmk,CompanyPhone01,CompanyPhone02
				,SlsCommPct,GainSlsCommPct,LossSlsCommPct,GainPurCommPct,LossPurCommPct
				,CommBasedOnQty,MinBFInsureSlsCommPct,MinSalesInsureSlsCommPct,SlsCommPctOnLowQty
				,OwnBySlspNo,Seller,CommAcctNo,CommProfitNo,CommBranchNo,InterCmpyDB,InterCmpyDBPath
                ,InterCmpySupNo,InterCmpyCustNo,InterCmpyLocNo,DftToTSOSales,PLAcctNo,PLProfitNo
                ,PLBranchNo,AvgCostGrpNo,AreaNo,PhysicalLoc,DiscType,NeedSelectCost,IsShareLoc
                ,TagPrefix,UseMarketCostInPo,PayTermNo,InterCompanyAcctno,InterCompanyProfitno,InterCompanyBranchno,CommissionCostMarkUpPct,NeedAddFreightCost,ServiceChargeRatePerMBF,ServiceChargeRate
                ,ServiceChargeRateUnit,IsTreatmentLocation,PringCustomValueOnPullCard,PrintItemNoInOeForm,PrintItemNoInPoForm,ArAnnualInterestRate, OverdueDaysForInterest, ApAnnualInterestRate, FreeDaysForInterest
                ,ServiceChargeRatePerMSF,FrtPerTruck,FrtPerRairCar,FrtPerMBF,FrtPerUnit,FrtSupNo,FrtType,CustNo,MinNonDiscAmtPerMBF,PrintPallet,FrtPerMSF,AvailIncludeQuotaQty,StoreAreaNo,IsQuarantineLocation,QuarantineLocationNo,IsTSO,NextOeNo,NextPoNo";
            }
        }
        #endregion

        #region mUserLocation_Fields
        public string mUserLocation_Fields
        {
            get
            {
                return @"UserName,Location,LocationDesc,Access";
            }
        }
        #endregion

        #region mBuyerLocation_Fields
        public string mLocationBuyer_Fields
        {
            get
            {
                return @"SlspNo,LocationNo,CommissionPct";
            }
        }
        #endregion

        #region mSellerLocation_Fields
        public string mLocationSeller_Fields
        {
            get
            {
                return @"SlspNo,LocationNo,CommissionPct";
            }
        }
        #endregion

        #region TSO_Fields
        public string TSO_Fields
        {
            get
            {
                return @"Locationno,CustNo";
            }
        }
        #endregion


        #region Select Location For LookUp
        public string SelectLocationForLookUp(bool bActive, bool bAccess)
        {
            string strSql = "Select LocationNo,LocationDesc from mlocation where 1=1 ";

            if (bActive)
            {
                strSql += " and active=1";
            }
            if (bAccess)
            {
                strSql += " and LocationNo in (select Location from mUserLocation where UserName='" + SysVar.UserName + "' and Access=1 )";
            }
            strSql += " order by LocationNo";
            return strSql;
        }
        #endregion

        #region Select by RecId
        public MLocationDEL SelectDataByRecID(DBAccess dBAccess, string strRecID)
        {
            MLocationDEL mLocationDEL = new MLocationDEL();
            string strSql = string.Empty;

            strSql = @"select * From mlocation";
            strSql += " where Recid=" + strRecID;

            dBAccess.GetDataSet(strSql, MLocationDEL.TABLENAME_MLOCATION, mLocationDEL);
            return mLocationDEL;
        }
        #endregion

        #region Select by Key
        public MLocationDEL SelectDataByKey(DBAccess dBAccess, string strLocNo)
        {
            MLocationDEL mLocationDEL = new MLocationDEL();
            string strSql = string.Empty;

            strSql = @"select * From mlocation";
            strSql += " where LocationNo='" + strLocNo + "' ";

            dBAccess.GetDataSet(strSql, MLocationDEL.TABLENAME_MLOCATION, mLocationDEL);
            return mLocationDEL;
        }
        #endregion

        #region Insert table by DataRow
        public void InsertMLocation(DBAccess dBAccess, MLocationDEL mLocationDEL)
        {
            string strSql = string.Empty;

            DataRow dr = mLocationDEL.Tables[MLocationDEL.TABLENAME_MLOCATION].Rows[0];
            strSql = BaseFunctions.GenerateSqlForDataAccess(FieldNames, MLocationDEL.TABLENAME_MLOCATION, dr, FormStatus.AddNew);
            dr[MLocationDEL.RECID] = dBAccess.GetIntValue(strSql);

            #region insert the user to the table mUserLocation
            this.InsertmUserLocationByDftAccess(dBAccess, dr[MLocationDEL.LOCATIONNO].ToString());
            #endregion

            #region Insert iminvloc (auto create items)
            if (CvtFunc.ToBoolean(dr[MLocationDEL.DEFCREATEIMLOCATIONS]))
                this.InsertImInvLoc(dBAccess, dr[MLocationDEL.LOCATIONNO].ToString());
            #endregion

        }
        #endregion

        #region Update table by DataRow
        public void UpdateMLocation(DBAccess dBAccess, MLocationDEL mLocationDEL)
        {
            string strSql = string.Empty;
            


            DataRow dr = mLocationDEL.Tables[MLocationDEL.TABLENAME_MLOCATION].Rows[0];

            string strLocDesc = DataFunctions.GetFieldValue(dBAccess, "mLocation", "LocationDesc", "LocationNo='" + CvtFunc.ToString(dr["LocationNo"]) + "'");
            bool blnAvailIncludeQuotaQty =CvtFunc.ToBoolean ( DataFunctions.GetFieldValue(dBAccess, "mLocation", "AvailIncludeQuotaQty", "LocationNo='" + CvtFunc.ToString(dr["LocationNo"]) + "'"));
            bool blnLocDescChanged = (strLocDesc.Trim().ToUpper() != CvtFunc.ToString(dr["LocationDesc"]).Trim().ToUpper());
            bool blnAvailIncludeQuotaQtyChanged = ( blnAvailIncludeQuotaQty != CvtFunc.ToBoolean(dr["AvailIncludeQuotaQty"]) );

            strSql = BaseFunctions.GenerateSqlForDataAccess(FieldNames, MLocationDEL.TABLENAME_MLOCATION, dr, FormStatus.Update);

            dr[MLocationDEL.RECSTAMP] = dBAccess.GetObjectValue(strSql);

            if (blnLocDescChanged)
            {
                strSql = "Update mUserLocation set LocationDesc='" + CvtFunc.ToString(dr["LocationDesc"]).Replace("'", "''") + "' where Location='" + CvtFunc.ToString(dr["LocationNo"]) + "'";
                dBAccess.ExecuteSql(strSql);
            }
            if (blnAvailIncludeQuotaQtyChanged)
            {
                strSql = "update iminvloc set AvailIncludeQuotaQty= '" + CvtFunc.ToBoolean(dr["AvailIncludeQuotaQty"]) + "'  where LocationNo='" + CvtFunc.ToString(dr["LocationNo"]) + "'";
                dBAccess.ExecuteSql(strSql);
            }
        }
        #endregion

        #region Delete table by Key
        public void DeleteMLocation(DBAccess dBAccess, string strRecId)
        {
            string strSql = "";
            string strLocNo = "";

            strLocNo = DataFunctions.GetFieldValue(dBAccess, MLocationDEL.TABLENAME_MLOCATION, MLocationDEL.LOCATIONNO, "RecId=" + strRecId);

            strSql = "delete from iminvloc WHERE LocationNo = '" + strLocNo + "';";
            strSql += "delete from iminvLocTotal WHERE LocationNo = '" + strLocNo + "';";
            strSql += "delete from iminvLocMisc WHERE LocationNo = '" + strLocNo + "';";
            strSql += "delete from iminvLocPrice WHERE LocationNo = '" + strLocNo + "';";
            strSql += "delete from mUserLocation WHERE Location = '" + strLocNo + "';";
            strSql += "delete from mAisle WHERE LocNo = '" + strLocNo + "';";
            strSql += "delete from mRack WHERE LocNo = '" + strLocNo + "';";
            strSql += "delete from mBinLoc WHERE LocNo = '" + strLocNo + "';";
            strSql += "delete from mLocation WHERE RecId = " + strRecId;

            dBAccess.ExecuteSql(strSql);

        }
        #endregion

        #region Insert mUserLocation by location using user default access value
        public void InsertmUserLocationByDftAccess(DBAccess dBAccess, string strLocNo)
        {
            string strSql = "";

            if (strLocNo.Trim() == "") return;

            string strLocDesc = DataFunctions.GetFieldValue(dBAccess, "mLocation", "LocationDesc", "LocationNo='" + strLocNo + "'");

            strSql = "Insert into mUserLocation(UserName,Location,LocationDesc,Access)";
            strSql += "Select UserName,'" + strLocNo + "','" + strLocDesc.Replace("'","''") + "',DflAccessToNewLoc from mUser";
            dBAccess.ExecuteSql(strSql);
        }
        #endregion

        #region Auto create items
        public void InsertImInvLoc(DBAccess dBAccess, string strLocNo)
        {
            string strSql = "";

            if (strLocNo.Trim() == "") return;

            strSql = "Insert into imInvLoc(ItemNo,LocationNo,KeyWord,Desc1,Desc2,Stocking,StockUnit,CostUnit,ProdType,Grouping,GroupingType,ConversionType,PlnNo,ItemStatus)";
            strSql += "Select ItemNo,'" + strLocNo + "',KeyWord,Desc1,Desc2,Stocking,StockUnit,CostUnit,ProdType,Grouping,GroupingType,ConversionType,PlnNo,ItemStatus from imInvMaster";
            dBAccess.ExecuteSql(strSql);
        }
        #endregion

        #region Insert mUserLocation by DataRow
        public void InsertmUserLocation(DBAccess dBAccess, DataRow dr)
        {
            string strSql = "";
            strSql = BaseFunctions.GenerateSqlForDataAccess(mUserLocation_Fields, "mUserLocation", dr, FormStatus.AddNew);
            dr["RECID"] = dBAccess.GetValue(strSql);
        }
        #endregion

        #region Update IminvMasterUnit by DataRow
        public void UpdatemUserLocation(DBAccess dBAccess, DataRow dr)
        {
            string strSql = "";
            strSql = BaseFunctions.GenerateSqlForDataAccess(mUserLocation_Fields, "mUserLocation", dr, FormStatus.Update);
            dr["RecStamp"] = dBAccess.GetObjectValue(strSql);
        }
        #endregion



        public void UpdatemLocationBuyer(DBAccess dBAccess, DataRow dr)
        {
            string strSql = "";
            strSql = BaseFunctions.GenerateSqlForDataAccess(mLocationBuyer_Fields, "mLocationBuyer", dr, FormStatus.Update);
            dr["RecStamp"] = dBAccess.GetObjectValue(strSql);
        }
        public void InsertmLocationBuyer(DBAccess dBAccess, DataRow dr)
        {
            string strSql = "";
            strSql = BaseFunctions.GenerateSqlForDataAccess(mLocationBuyer_Fields, "mLocationBuyer", dr, FormStatus.AddNew);
            dr["RECID"] = dBAccess.GetValue(strSql);
        }

        public void UpdatemLocationSeller(DBAccess dBAccess, DataRow dr)
        {
            string strSql = "";
            strSql = BaseFunctions.GenerateSqlForDataAccess(mLocationSeller_Fields, "mLocationSeller", dr, FormStatus.Update);
            dr["RecStamp"] = dBAccess.GetObjectValue(strSql);
        }
        public void InsertmLocationSeller(DBAccess dBAccess, DataRow dr)
        {
            string strSql = "";
            strSql = BaseFunctions.GenerateSqlForDataAccess(mLocationSeller_Fields, "mLocationSeller", dr, FormStatus.AddNew);
            dr["RECID"] = dBAccess.GetValue(strSql);
        }

        public void UpdateTSO(DBAccess dBAccess, DataRow dr)
        {
            string strSql = "";
            strSql = BaseFunctions.GenerateSqlForDataAccess(TSO_Fields, "mTSOLocCustomer", dr, FormStatus.Update);
            dr["RecStamp"] = dBAccess.GetObjectValue(strSql);
        }
        public void InsertTSO(DBAccess dBAccess, DataRow dr)
        {
            string strSql = "";
            strSql = BaseFunctions.GenerateSqlForDataAccess(TSO_Fields, "mTSOLocCustomer", dr, FormStatus.AddNew);
            dr["RECID"] = dBAccess.GetValue(strSql);
        }

        public void DeleteSeller(DBAccess dBAccess, string strRecId)
        {
            string strSql = "";

            strSql += "delete from mLocationSeller WHERE RecId = '" + strRecId + "'";

            dBAccess.ExecuteSql(strSql);
        }
        public void DeleteTSO(DBAccess dBAccess, string strRecId)
        {
            string strSql = "";

            strSql += "delete from mTSOLocCustomer WHERE RecId = '" + strRecId + "'";

            dBAccess.ExecuteSql(strSql);
        }
        public void DeleteBuyer(DBAccess dBAccess, string strRecId)
        {
            string strSql = "";

            strSql += "delete from mLocationBuyer WHERE RecId = '" + strRecId + "'";

            dBAccess.ExecuteSql(strSql);
        }

        #region Delete Aisle
        public void DeleteAisle(DBAccess dBAccess, string strLocNo, string strAisleNo)
        {
            string strSql = string.Empty;

            strSql = @"DELETE FROM mAisle WHERE LocNo='" + strLocNo + "' and AisleNo='" + strAisleNo + "'";
            dBAccess.ExecuteSql(strSql);

            strSql = @"DELETE FROM mRack WHERE LocNo='" + strLocNo + "' and AisleNo='" + strAisleNo + "'";
            dBAccess.ExecuteSql(strSql);

            strSql = "delete from mBinLoc where LocNo='" + strLocNo + "' and AisleNo='" + strAisleNo + "'";
            dBAccess.ExecuteSql(strSql);
        }
        #endregion

        #region Update Aisle
        public void UpdateAisle(DBAccess dBAccess, DataRow dr)
        {
            string strSql = "";
            string strUpdateFields = "SortNo,AisleNo,Description,LocNo,FromRackNo,ToRackNo";
            strSql = BaseFunctions.GenerateSqlForDataAccess(strUpdateFields, "mAisle", dr, FormStatus.Update);
            dr["RECSTAMP"] = dBAccess.GetObjectValue(strSql);

        }
        #endregion

        #region Insert Aisle
        public void InsertAisle(DBAccess dBAccess, DataRow dr)
        {

            string strSql = "";
            string strUpdateFields = "SortNo,AisleNo,Description,LocNo,FromRackNo,ToRackNo";
            strSql = BaseFunctions.GenerateSqlForDataAccess(strUpdateFields, "mAisle", dr, FormStatus.AddNew);
            dr["RecID"] = dBAccess.GetValue(strSql);

        }


        #endregion


        #region Delete Rack
        public void DeleteRack(DBAccess dBAccess, string strLocNo, string strAisleNo, string strRackNo)
        {
            string strSql = string.Empty;

            strSql = @"DELETE FROM mRack WHERE LocNo='" + strLocNo + "' and AisleNo='" + strAisleNo + "' and rackNo='" + strRackNo + "'";
            dBAccess.ExecuteSql(strSql);

            strSql = "delete from mBinLoc where LocNo='" + strLocNo + "' and AisleNo='" + strAisleNo + "' and rackNo='" + strRackNo + "'";
            dBAccess.ExecuteSql(strSql);
        }
        #endregion

        #region Update Rack
        public void UpdateRack(DBAccess dBAccess, DataRow dr)
        {
            string strSql = "";
            string strUpdateFields = "SortNo,RackNo,Description,AisleNo,LocNo,FromShelfNo,ToShelfNo,BinCountPerShelf";
            strSql = BaseFunctions.GenerateSqlForDataAccess(strUpdateFields, "mRack", dr, FormStatus.Update);
            dr["RECSTAMP"] = dBAccess.GetObjectValue(strSql);

        }
        #endregion

        #region Insert Rack
        public void InsertRack(DBAccess dBAccess, DataRow dr)
        {

            string strSql = "";
            string strUpdateFields = "SortNo,RackNo,Description,AisleNo,LocNo,FromShelfNo,ToShelfNo,BinCountPerShelf";
            strSql = BaseFunctions.GenerateSqlForDataAccess(strUpdateFields, "mRack", dr, FormStatus.AddNew);
            dr["RecID"] = dBAccess.GetValue(strSql);

        }


        #endregion



        #region Delete Bin
        public void DeleteBin(DBAccess dBAccess, int iRecID)
        {
            string strSql = string.Empty;

            strSql = @"DELETE FROM mBinLoc WHERE recid=" + iRecID.ToString();
            dBAccess.ExecuteSql(strSql);
        }
        #endregion

        #region Update Bin
        public void UpdateBin(DBAccess dBAccess, DataRow dr)
        {
            string strSql = "";
            string strUpdateFields = "SortNo,LocNo,BinLocNo,Bins,AisleNo,RackNo,Shelf,MaxPallets,MaxPalletUnit,Length,Width,height,Volume,AbsHeight,InputAreaNo,PickingAreaNo";
            strSql = BaseFunctions.GenerateSqlForDataAccess(strUpdateFields, "mBinLoc", dr, FormStatus.Update);
            dr["RECSTAMP"] = dBAccess.GetObjectValue(strSql);

        }
        #endregion

        #region Insert Bin
        public void InsertBin(DBAccess dBAccess, DataRow dr)
        {

            string strSql = "";
            string strUpdateFields = "SortNo,LocNo,BinLocNo,Bins,AisleNo,RackNo,Shelf,MaxPallets,MaxPalletUnit,Length,Width,height,Volume,AbsHeight,InputAreaNo,PickingAreaNo";
            strSql = BaseFunctions.GenerateSqlForDataAccess(strUpdateFields, "mBinLoc", dr, FormStatus.AddNew);
            dr["RecID"] = dBAccess.GetValue(strSql);

        }


        #endregion


        public void GenerateBinLoc(DBAccess dbcon, string strLocNo)
        {
            string strSql = "select * from mRack where locno='" + strLocNo + "' and FromShelfNo <>'' and ToShelfNo<>'' and BinCountPerShelf>0";
            DataView dvRack = dbcon.GetDataTable(strSql).DefaultView;
            foreach (DataRowView drw in dvRack)
            {
                char chFrom = CvtFunc.ToString(drw["FromShelfNo"]).Trim().ToUpper().ToCharArray()[0];
                char chTo = CvtFunc.ToString(drw["ToShelfNo"]).Trim().ToUpper().ToCharArray()[0];
                int iBinCount = CvtFunc.ToInt(drw["BinCountPerShelf"]);


                string strSortNoInAisle = DataFunctions.GetFieldValue(dbcon, "mAisle", "SortNo", "AisleNo='" + drw["AisleNo"].ToString().Trim() + "'").TrimEnd();
                string strSortNo = strSortNoInAisle + CvtFunc.ToString(drw["SortNo"]).Trim();
                if (strSortNo.Length > 10) strSortNo = strSortNo.Substring(0, 10);
                for (char ch1 = chFrom; ch1 <= chTo; ch1++)
                {
                    for (int i = 1; i <= iBinCount; i++)
                    {
                        string strShelf = char.ConvertFromUtf32(ch1);
                        string strBinLocNo = drw["AisleNo"].ToString().Trim() + "-" + drw["RackNo"].ToString().Trim() + "-" + strShelf + "-" + BaseFunctions.FormatWithZero(i.ToString(), 2);
                        if (DataFunctions.CheckRecordExist(dbcon, "mBinLoc", strLocNo, "LocNo", "AisleNo='" + drw["AisleNo"].ToString().Trim() + "' and RackNo='" + drw["RackNo"].ToString().Trim() + "' and Shelf='" + strShelf + "' and Bins='" + BaseFunctions.FormatWithZero(i.ToString(), 2) + "'")) continue;
                        strSql = "Insert into mBinLoc (LocNo,BinLocNo,Bins,AisleNo,RackNo,Shelf,SortNo) values ('" + strLocNo + "','" + strBinLocNo + "','" + BaseFunctions.FormatWithZero(i.ToString(), 2) + "','"
                                   + drw["AisleNo"].ToString().Trim() + "','" + drw["RackNo"].ToString().Trim() + "','" + strShelf + "','" + strSortNo + "')";
                        dbcon.ExecuteSql(strSql);

                    }
                }
            }

        }
    }
}